﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDAOrderDetailItem
    {

        public static Boolean Delete(OrderDetailItem orderDetailItem, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " DELETE orderdetailitem " +
                              " WHERE ID = " + orderDetailItem.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(OrderDetailItem orderDetailItem, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " UPDATE orderdetailitem SET " +
                              "  OrderDetailID = " + ((string.IsNullOrEmpty(orderDetailItem.OrderDetail.ID.ToString()) || (orderDetailItem.OrderDetail.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetailItem.OrderDetail.ID.ToString()) +
                              " ,ProductExtraID = " + ((string.IsNullOrEmpty(orderDetailItem.ProductExtra.ID.ToString()) || (orderDetailItem.ProductExtra.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetailItem.ProductExtra.ID.ToString()) +
                              " ,Price = " + ((string.IsNullOrEmpty(orderDetailItem.Price.ToString()) || (orderDetailItem.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetailItem.Price.ToString().Replace(',', '.')) +
                              " WHERE ID = " + orderDetailItem.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(OrderDetailItem orderDetailItem, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO orderdetailitem (OrderDetailID, ProductExtraID, Price) " +
                              " VALUES( " + ((string.IsNullOrEmpty(orderDetailItem.OrderDetail.ID.ToString()) || (orderDetailItem.OrderDetail.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetailItem.OrderDetail.ID.ToString()) +
                              " , " + ((string.IsNullOrEmpty(orderDetailItem.ProductExtra.ID.ToString()) || (orderDetailItem.ProductExtra.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetailItem.ProductExtra.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(orderDetailItem.Price.ToString()) || (orderDetailItem.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderDetailItem.Price.ToString().Replace(',', '.')) +
                              ")";

            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                orderDetailItem.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<OrderDetailItem> Select(OrderDetailItem orderDetailItem)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT odi.* " +
            " FROM orderdetailitem odi " +
            " WHERE ((odi.ID = " + orderDetailItem.ID.ToString() + ") OR (" + orderDetailItem.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "ORDER BY odi.ID";

            SqlCommand cmd = new SqlCommand(_cmdText, con);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<OrderDetailItem> _orderDetailItemList = new List<OrderDetailItem>();

            OrderDetailItem _orderDetailItem;
            foreach (DataRow row in tbl.Rows)
            {
                _orderDetailItem = new OrderDetailItem();

                _orderDetailItem.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _orderDetailItem.OrderDetail = new OrderDetail();
                _orderDetailItem.OrderDetail.ID = (row["OrderDetailID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["OrderDetailID"]));

                _orderDetailItem.ProductExtra = new ProductExtra();
                _orderDetailItem.ProductExtra.ID = (row["ProductExtraID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ProductExtraID"]));
                
                _orderDetailItem.Price = (row["Price"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price"]));

                _orderDetailItemList.Add(_orderDetailItem);
            }

            con.Close();
            return _orderDetailItemList;
        }
    }
}